import requests
import datetime
import pandas as pd
import pymysql
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class WeatherUtils(object):
    """天气数据工具类，用于获取和处理天气数据"""
    
    def __init__(self):
        # 初始化API地址和需要查询的日期列表
        self.url = 'http://vi.yiketianqi.com/api'
        self.dates = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]
        self.weather_data = None  # 用于存储获取的天气数据
    
    def get_data(self):
        """从天气API获取数据并保存为DataFrame"""
        data_list = []  # 临时存储获取的数据
        
        for d in self.dates:
            # 配置API请求参数
            config = {
                'appid': "88249599",  # 替换为自己注册的appid
                'appsecret': "BAYzijn",
                'version': "history",
                'year': d[:4],  # 提取年份
                'month': d[5:7],  # 提取月份
                'city': "南昌"  # 查询城市
            }
            
            try:
                # 发送GET请求获取天气数据
                res = requests.get(self.url, params=config)
                res.raise_for_status()  # 检查请求是否成功
                res_data = res.json()  # 解析JSON响应
                
                # 处理返回的天气数据
                for item in res_data['data']:
                    data_list.append({
                        'date': datetime.datetime.strptime(item['ymd'], '%Y-%m-%d'),  # 转换日期格式
                        'bWendu': item['bWendu'],  # 最高温度
                        'yWendu': item['yWendu'],  # 最低温度
                        'tianqi': item['tianqi'],  # 天气状况
                        'fengli': item['fengli'],  # 风力
                    })
                    
            except requests.exceptions.RequestException as e:
                print(f"获取{d}的天气数据时出错: {e}")
                continue
        
        # 将数据转换为DataFrame并保存
        self.weather_data = pd.DataFrame(data_list)
        self.weather_data.to_csv('./data/weather_data.csv', index=False)
        return self.weather_data


class MysqlUtils(object):
    """MySQL数据库工具类，用于处理景区数据"""
    
    def __init__(self):
        try:
            # 连接MySQL数据库
            self.conn = pymysql.connect(
                host='127.0.0.1',  # 数据库地址
                user='root',      # 用户名
                password='root',  # 密码
                database='scenic', # 数据库名
                port=3306,         # 端口
                charset='utf8'     # 字符编码
            )
        except pymysql.Error as e:
            print(f"连接MySQL数据库失败: {e}")
            raise
    
    def is_holiday(self, date):
        """判断给定日期是否为节假日"""
        holidays = [
            '2024-09-05', '2024-10-01', '2024-10-02', '2024-10-03',
            '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07',
            '2025-01-01', '2025-01-02', '2025-01-03'
        ]
        return 1 if str(date) in holidays else 0  # 是节假日返回1，否则返回0
    
    def get_scenic_data(self, weather_data):
        """获取景区数据并与天气数据合并处理"""
        try:
            # 使用游标执行SQL查询
            with self.conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
                # SQL查询：获取每日订单数量
                sql = """
                SELECT DATE(g.create_time) as date, count(*) as count
                FROM order_user_date_rel g
                WHERE DATE(g.create_time) < '2025-01-01' 
                GROUP BY date
                """
                cursor.execute(sql)
                ret = cursor.fetchall()
                df = pd.DataFrame(ret)  # 将结果转换为DataFrame
                
                # 合并天气数据和景区数据
                weather_data['date'] = pd.to_datetime(weather_data['date'])
                df['date'] = pd.to_datetime(df['date'])
                # 使用左连接保留所有天气数据
                df_merged = pd.merge(weather_data, df, on='date', how='left')
                df_merged.set_index('date', inplace=True)  # 设置日期为索引
                
                # 清洗温度数据（移除℃符号并转换为整数）
                df_merged['bWendu'] = df_merged['bWendu'].str.replace("℃", "").astype(int)
                df_merged['yWendu'] = df_merged['yWendu'].str.replace("℃", "").astype(int)
                
                # 添加时间特征
                df_merged['dow'] = df_merged.index.dayofweek  # 星期几（0-6）
                df_merged['month'] = df_merged.index.month    # 月份
                df_merged['is_holiday'] = df_merged.index.map(self.is_holiday)  # 是否节假日
                
                # 对分类特征进行独热编码
                df_merged = pd.get_dummies(df_merged, columns=['dow', 'month', 'tianqi', 'fengli'], dtype=int)
                
                # 归一化游客数量
                if 'count' in df_merged.columns:
                    scaler = MinMaxScaler()
                    df_merged['count'] = scaler.fit_transform(df_merged[['count']])
                    dump(scaler, './data/scaler.joblib')  # 保存归一化模型
                
                # 保存天气特征
                weather_features = df_merged[['bWendu', 'yWendu']]
                dump(weather_features, './data/weather_features.joblib')
                
                # 打印前几行数据并保存到CSV
                print(df_merged.head())
                df_merged.to_csv('./data/scenic_data.csv')
                
                return df_merged
                
        except pymysql.Error as e:
            print(f"数据库操作出错: {e}")
            return None
        finally:
            self.conn.close()  # 确保关闭数据库连接


if __name__ == '__main__':
    # 第一步：获取天气数据
    wu = WeatherUtils()
    weather_df = wu.get_data()
    
    # 第二步：获取并处理景区数据（需要天气数据）
    mu = MysqlUtils()
    if weather_df is not None:
        scenic_df = mu.get_scenic_data(weather_df)